--drop table if exists dongfeng.navinfo_stage_d009;
--创建外部表
create external table if not exists dongfeng.navinfo_stage_d009 (
                terminalid                                         BIGINT              COMMENT '',
                serialnumber                    int    comment '流水号',
                receivedate                     bigint comment '接收时间',
                gpstime                         bigint comment '时间',
                latitude                        int    comment '纬度',
                longitude                       int    comment '经度',
                originalLng                     int    comment '原始纬度',
                originalLat                     int    comment '原始经度',
                height                          int    comment '高程，海拔高度，单位为米',
                direction                       int    comment '方向(0~359)正北为0，顺时针',
                depotlogo                       int    comment '区分车厂标志(1：一汽（新协议）、 2：一汽（大陆）、3：青汽、4：东风、5：山西大运（重卡）、6：后装)',
                rapidaccelerationduration       int     comment '单次急加速累计时间',
                rapidaccelerationfuel           double  comment '单次急加速累计油耗值',
                rapidaccelerationstatus         int    comment '急加速事件状态',
                rapiddecelerationduration       int    comment '单次急减速累计时间',
                rapiddecelerationstatus         int    comment '急减速事件',
                overspeedfuelconsumption        double  comment '单次超速驾驶累计油耗值',
                overspeedstatus                 int    comment '超速驾驶事件',
                overspeedduration               int    comment '单次超速驾驶累计时间',
                superlongidlestatus             int    comment '超长怠速事件',
                superlongidleduration           int    comment '单次超长怠速累计时间',
                superlongidlefuelConsumption    double  comment '单次超长怠速累计油耗',
                idlespeedstatus                 int    comment '怠速空调事件',
                idlespeedduration               int    comment '单次怠速空调累计发生时间',
                idlespeedfuel                   double  comment '单次怠速空调累计油耗',
                bigthrottlestatus               int    comment '大油门事件',
                bigthrottleduration             int    comment '单次大油门累计发生时间',
                bigthrottlefuel                 double  comment '单次大油门累计油耗',
                neutraltaxistatus               int    comment '空挡滑行事件',
                neutraltaxiduration             int    comment '空挡滑行时间',
                fatiguedrivingtime              int    comment '疲劳驾驶累计时间',
                fatiguedrivingstatus            int    comment '疲劳驾驶事件',
                part_time                       INT    COMMENT '分区时间'
) row format delimited fields terminated by '\001' STORED AS TEXTFILE location '/user/data/dongfeng/stage_import_d009';

--创建目标表
create table if not exists dongfeng.navinfo_parquet_d009(
                       terminalid                                         BIGINT              COMMENT '',
                       serialnumber                    int    comment '流水号',
                       receivedate                     bigint comment '接收时间',
                       gpstime                         bigint comment '时间',
                       latitude                        int    comment '纬度',
                       longitude                       int    comment '经度',
                       originalLng                     int    comment '原始纬度',
                       originalLat                     int    comment '原始经度',
                       height                          int    comment '高程，海拔高度，单位为米',
                       direction                       int    comment '方向(0~359)正北为0，顺时针',
                       depotlogo                       int    comment '区分车厂标志(1：一汽（新协议）、 2：一汽（大陆）、3：青汽、4：东风、5：山西大运（重卡）、6：后装)',
                       rapidaccelerationduration       int     comment '单次急加速累计时间',
                       rapidaccelerationfuel           double  comment '单次急加速累计油耗值',
                       rapidaccelerationstatus         int    comment '急加速事件状态',
                       rapiddecelerationduration       int    comment '单次急减速累计时间',
                       rapiddecelerationstatus         int    comment '急减速事件',
                       overspeedfuelconsumption        double  comment '单次超速驾驶累计油耗值',
                       overspeedstatus                 int    comment '超速驾驶事件',
                       overspeedduration               int    comment '单次超速驾驶累计时间',
                       superlongidlestatus             int    comment '超长怠速事件',
                       superlongidleduration           int    comment '单次超长怠速累计时间',
                       superlongidlefuelConsumption    double  comment '单次超长怠速累计油耗',
                       idlespeedstatus                 int    comment '怠速空调事件',
                       idlespeedduration               int    comment '单次怠速空调累计发生时间',
                       idlespeedfuel                   double  comment '单次怠速空调累计油耗',
                       bigthrottlestatus               int    comment '大油门事件',
                       bigthrottleduration             int    comment '单次大油门累计发生时间',
                       bigthrottlefuel                 double  comment '单次大油门累计油耗',
                       neutraltaxistatus               int    comment '空挡滑行事件',
                       neutraltaxiduration             int    comment '空挡滑行时间',
                       fatiguedrivingtime              int    comment '疲劳驾驶累计时间',
                       fatiguedrivingstatus            int    comment '疲劳驾驶事件',
                       create_time                     STRING              COMMENT '数据创建时间'
) partitioned by (part_time int) stored as Parquet TBLPROPERTIES('parquet.compression'='SNAPPY');
